﻿using System;
using System.Collections;
using System.Data;
using CE.Database;


/// <summary>
/// Summary description for WegoFlightsDB
/// </summary>
/// 
/// 
namespace CE.Wego
{
    public class WegoFlightsDB
    {
        public WegoFlightsDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 ClearTables(Int64 ViewId)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocWegoFlightsClearTables]";

            SortedList _sPara = new SortedList();
            _sPara.Add("@ViewId", ViewId);

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 CreateMainTable()
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocWegoFlightsCreateTempTable";

            SortedList _sPara = new SortedList();
            _sPara.Add("@Id", "1");

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }


        public Int64 Insert(WegoFlightsInfo wegoflights)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocWegoFlightsInsertSingleItem";

            SortedList _sPara = new SortedList();
            #region Parameter

            _sPara.Add("@ViewId", wegoflights.ViewId);
            _sPara.Add("@BookingCode", wegoflights.BookingCode);
            _sPara.Add("@ID", wegoflights.ID);
            _sPara.Add("@Airlines", wegoflights.Airlines);
            _sPara.Add("@AirlineCode", wegoflights.AirlineCode);
            _sPara.Add("@LocationFrom", wegoflights.LocationFrom);
            _sPara.Add("@LocationTo", wegoflights.LocationTo);
            _sPara.Add("@AirportFrom", wegoflights.AirportFrom);
            _sPara.Add("@AirportTo", wegoflights.AirportTo);
            _sPara.Add("@OutboundDepartureTime", wegoflights.OutboundDepartureTime);
            _sPara.Add("@OutboundArrivalTime", wegoflights.OutboundArrivalTime);
            _sPara.Add("@InboundDepartureTime", wegoflights.InboundDepartureTime);
            _sPara.Add("@InboundArrivalTime", wegoflights.InboundArrivalTime);
            _sPara.Add("@OutboundFlightNumber", wegoflights.OutboundFlightNumber);
            _sPara.Add("@InboundFlightNumber", wegoflights.InboundFlightNumber);
            _sPara.Add("@OutboundAirports", wegoflights.OutboundAirports);
            _sPara.Add("@InboundAirports", wegoflights.InboundAirports);
            _sPara.Add("@OutboundDuration", wegoflights.OutboundDuration);
            _sPara.Add("@InboundDuration", wegoflights.InboundDuration);
            _sPara.Add("@FareClassInbound", wegoflights.FareClassInbound);
            _sPara.Add("@FareClassOutbound", wegoflights.FareClassOutbound);
            _sPara.Add("@Amount", wegoflights.Amount);
            _sPara.Add("@PricePerPassenger", wegoflights.PricePerPassenger);
            _sPara.Add("@totalAmount", wegoflights.TotalAmount);
            _sPara.Add("@totalPricePerPassenger", wegoflights.TotalPricePerPassenger);
            _sPara.Add("@CurrencyCode", wegoflights.CurrencyCode);
            _sPara.Add("@IncludesTax", wegoflights.IncludesTax);
            _sPara.Add("@Provider", wegoflights.Provider);
            _sPara.Add("@OutboundNoOfStops", wegoflights.OutboundNoOfStops);
            _sPara.Add("@InboundNoOfStops", wegoflights.InboundNoOfStops);
            _sPara.Add("@OutboundLayOverInMin", wegoflights.OutboundLayOverInMin);
            _sPara.Add("@InboundLayOverInMin", wegoflights.InboundLayOverInMin);
            _sPara.Add("@OutboundConnectingDetails", wegoflights.OutboundConnectingDetails);
            _sPara.Add("@InboundConnectingDetails", wegoflights.InboundConnectingDetails);

    
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 InsertToAirportLocation(Int64 ViewId, string BookingCode, string ID, string LocationCode, string LocationName)
        {
            
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocWegoFlightsAirportLocationInsertSingleItem";

            SortedList _sPara = new SortedList();
            #region Parameter

            _sPara.Add("@ViewId", ViewId);
            _sPara.Add("@BookingCode", BookingCode);
            _sPara.Add("@ID", ID);
            _sPara.Add("@LocationCode", LocationCode);
            _sPara.Add("@LocationName", LocationName);
           
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 InsertSearch(string Origin, string Destination, DateTime DepartureDate, DateTime ReturnDate, Int64 UserId)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocWegoFlightsSearchInsertSingleItem";

            SortedList _sPara = new SortedList();
            #region Parameter

            _sPara.Add("@Origin", Origin);
            _sPara.Add("@Destination", Destination);
            _sPara.Add("@DepartureDate ", DepartureDate);
            _sPara.Add("@ReturnDate", ReturnDate);
            _sPara.Add("@RefCEUserId", UserId);

            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 UpdateSearch(Int64 ViewId, Int64 RandValue)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocWegoFlightsSearchUpdateSingleItem";

            SortedList _sPara = new SortedList();
            #region Parameter

            _sPara.Add("@FlightSearchId", ViewId);
            _sPara.Add("@RandValue", RandValue);

            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public WegoFlightsInfoList GetWegoFlightsInfoList(Int64 ViewID)
        {
            WegoFlightsInfoList _wegoflightslist = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocWegoFlightsSelectItemList]";

            SortedList _sPara = new SortedList();
            _sPara.Add("@ViewId", ViewID);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _sPara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    for (int i = 0; i < _dt.Rows.Count; i++)
                    {
                        WegoFlightsInfo _wegoflights = new WegoFlightsInfo();
                        _wegoflights = FillDataRecord(_dt, i);
                        _wegoflightslist.Add(_wegoflights);
                    }
                }
            }
             return _wegoflightslist;
        }

        public DataTable GetWegoFlightsInfoDetails(Int64 ViewID, string Airlines)
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("Details");
            string _spName = "sprocWegoFlightsSelectItemDetails";
            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@ViewId", ViewID);
            _sPara.Add("@Airlines", Airlines);
            #endregion

            _dtResult = _db.GetDataTableBySP(_spName, _sPara);
            return _dtResult;
        }

        public DataTable GetWegoFlightsInfoDistinctFlights(Int64 ViewID, string Airlines)
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("Details");
            string _spName = "sprocWegoFlightsSelectDistinctFlights";
            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@ViewId", ViewID);
            _sPara.Add("@Airlines", Airlines);
            #endregion

            _dtResult = _db.GetDataTableBySP(_spName, _sPara);
            return _dtResult;
        }

        public DataTable GetWegoSummary(Int64 ViewID)
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("Summary");
            string _spName = "sprocWegoFlightsSelectItemSummary";
            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@ViewId", ViewID);
            #endregion

            _dtResult = _db.GetDataTableBySP(_spName, _sPara);
            return _dtResult;
        }

        public string GetAirportLocation(Int64 ViewID, string LocationCode)
        {
            string airportlocation = "";
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("AirLocation");
            string _spName = "sprocWegoFlightsAirportLocationSelectSingleItem";
            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@ViewId", ViewID);
            _sPara.Add("@LocationCode", LocationCode);
            #endregion

            _dtResult = _db.GetDataTableBySP(_spName, _sPara);

            if (_dtResult.Rows.Count > 0)
                airportlocation = _dtResult.Rows[0]["LocationName"].ToString();

            return airportlocation;
        }


        private WegoFlightsInfo FillDataRecord(DataTable myDataTable, int rowId)
        {
            WegoFlightsInfo wegoflights = new WegoFlightsInfo();
            wegoflights.ViewId = Convert.ToInt64(myDataTable.Rows[rowId]["ViewId"].ToString());
            wegoflights.FlightsID = Convert.ToInt32(myDataTable.Rows[rowId]["FlightsID"].ToString());
            wegoflights.ID = myDataTable.Rows[rowId]["ID"].ToString();
            wegoflights.Airlines = myDataTable.Rows[rowId]["Airlines"].ToString();
            wegoflights.AirlineCode = myDataTable.Rows[rowId]["AirlineCode"].ToString();
            wegoflights.LocationFrom = myDataTable.Rows[rowId]["LocationFrom"].ToString();
            wegoflights.LocationTo = myDataTable.Rows[rowId]["LocationTo"].ToString();
            wegoflights.AirportFrom = myDataTable.Rows[rowId]["AirportFrom"].ToString();
            wegoflights.AirportTo = myDataTable.Rows[rowId]["AirportTo"].ToString();

            wegoflights.OutboundDepartureTime = myDataTable.Rows[rowId]["OutboundDepartureTime"].ToString();
            wegoflights.OutboundArrivalTime = myDataTable.Rows[rowId]["OutboundArrivalTime"].ToString();
            wegoflights.InboundDepartureTime = myDataTable.Rows[rowId]["InboundDepartureTime"].ToString();
            wegoflights.InboundArrivalTime = myDataTable.Rows[rowId]["InboundArrivalTime"].ToString();

            wegoflights.OutboundFlightNumber = myDataTable.Rows[rowId]["OutboundFlightNumber"].ToString();
            wegoflights.InboundFlightNumber = myDataTable.Rows[rowId]["InboundFlightNumber"].ToString();
            wegoflights.OutboundAirports = myDataTable.Rows[rowId]["OutboundAirports"].ToString();
            wegoflights.InboundAirports = myDataTable.Rows[rowId]["InboundAirports"].ToString();

            wegoflights.OutboundDuration = myDataTable.Rows[rowId]["OutboundDuration"].ToString();
            wegoflights.InboundDuration = myDataTable.Rows[rowId]["InboundDuration"].ToString();

            wegoflights.FareClassInbound = myDataTable.Rows[rowId]["FareClassInbound"].ToString();
            wegoflights.FareClassOutbound = myDataTable.Rows[rowId]["FareClassOutbound"].ToString();

            wegoflights.Amount = float.Parse(myDataTable.Rows[rowId]["Amount"].ToString());
            wegoflights.PricePerPassenger = float.Parse(myDataTable.Rows[rowId]["PricePerPassenger"].ToString());
            wegoflights.TotalAmount = float.Parse(myDataTable.Rows[rowId]["totalAmount"].ToString());
            wegoflights.TotalPricePerPassenger = float.Parse(myDataTable.Rows[rowId]["totalPricePerPassenger"].ToString());

            wegoflights.CurrencyCode = myDataTable.Rows[rowId]["CurrencyCode"].ToString();
            wegoflights.IncludesTax = bool.Parse(myDataTable.Rows[rowId]["IncludesTax"].ToString());
            wegoflights.Provider = myDataTable.Rows[rowId]["Provider"].ToString();

            wegoflights.OutboundNoOfStops = myDataTable.Rows[rowId]["OutboundNoOfStops"].ToString();
            wegoflights.InboundNoOfStops = myDataTable.Rows[rowId]["InboundNoOfStops"].ToString();
            wegoflights.OutboundLayOverInMin = myDataTable.Rows[rowId]["OutboundLayOverInMin"].ToString();
            wegoflights.InboundLayOverInMin = myDataTable.Rows[rowId]["InboundLayOverInMin"].ToString();

            wegoflights.OutboundConnectingDetails = myDataTable.Rows[rowId]["OutboundConnectingDetails"].ToString();
            wegoflights.InboundConnectingDetails = myDataTable.Rows[rowId]["InboundConnectingDetails"].ToString();
                  
            return wegoflights;
        }
    }
}